package main;

import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import system.Book;
import system.BookProxy;
import system.Order;
import system.OrderProxy;
import system.OrderState;
import system.User;
import system.UserIdentity;
import system.UserProxy;
import util.StringParser;
import db.DBConnection;

public class DBUtil implements Service {
	private BookProxy mBookProxy;
	private OrderProxy mOrderProxy;
	private UserProxy mUserProxy;
	private DBConnection mDBConnection;
	
	public DBUtil(BookProxy pBookProxy, OrderProxy pOrderProxy, UserProxy pUserProxy, DBConnection pConnection) {
		mBookProxy = pBookProxy;
		mOrderProxy = pOrderProxy;
		mUserProxy = pUserProxy;
		mDBConnection = pConnection;
	}
	
	public BookProxy getBookProxy() {
		return mBookProxy;
	}


	public OrderProxy getOrderProxy() {
		return mOrderProxy;
	}


	public UserProxy getUserProxy() {
		return mUserProxy;
	}


	public DBConnection getDBConnection() {
		return mDBConnection;
	}

	@Override
	public User insertUser(String pAccount, String pPassword, UserIdentity pIdentity, String pName, String pPhone, String pAddress) throws SQLException {
		User u = new User(pAccount, pPassword, pIdentity, pName, pPhone, pAddress);
		
		return mUserProxy.insert(u, mDBConnection);
	}

	@Override
	public int drop(User pUser) throws SQLException {
		return mUserProxy.drop(pUser, mDBConnection);
	}

	@Override
	public int update(User pUser) throws SQLException {
		return mUserProxy.update(pUser, mDBConnection);
	}

	@Override
	public List<User> queryUser(String pAccount, String pPassword, String pName, UserIdentity pIdentity, float pCreditPoint) throws SQLException {
		StringBuffer condition = new StringBuffer();
		if (pAccount != null) {
			condition.append("account = '" + pAccount +"' AND ");
		}
		
		if (pAccount != null) {
			condition.append("password = '" + pPassword +"' AND ");
		}
		
		if (pName != null) {
			condition.append("name='" + pName + "' AND ");
		}
		
		if (pIdentity != null) {
			condition.append("user_identity='" + pIdentity + "' AND ");
		}
		
		if (pCreditPoint >= 0) {
			condition.append("pCreditPoint>=" + pCreditPoint + "' AND ");
		}
		
		if (condition.length() > 5) {
			condition.delete(condition.length() - 5, condition.length());
			return mUserProxy.query(mDBConnection, condition.toString());
		}
		
		return new LinkedList<>();
	}

	@Override
	public Book insertBook(String pName, String pISBN, String pAuthor,
			String pPress, String pDescription, String pCatagory,
			Date pPublishDate, float pPrice, int pCount) throws SQLException {
		Book book = new Book(pName, pISBN, pAuthor, pPress, pDescription, pCatagory, pPublishDate, pPrice, pCount);
		
		return mBookProxy.insert(book, mDBConnection);
	}

	@Override
	public int drop(Book pBook) throws SQLException {
		return mBookProxy.drop(pBook, mDBConnection);
	}

	@Override
	public int update(Book pBook) throws SQLException {
		return mBookProxy.update(pBook, mDBConnection);
	}

	@Override
	public List<Book> queryBook(String pKeywordsOfBookName, String pAuthor) throws SQLException {
		StringBuffer sql = new StringBuffer();
		if (pKeywordsOfBookName != null && pKeywordsOfBookName.length() > 0) {
			String[] keywords = pKeywordsOfBookName.split("\\s");
			sql.append("name LIKE '");
			for (String s : keywords) sql.append("%" + s);
			sql.append("%' AND ");
		}
		
		if (pAuthor != null) {
			String[] keywords = pAuthor.split("\\s");
			sql.append("author LIKE '");
			for (String s : keywords) sql.append("%" + s);
			sql.append("%' AND ");
		}
		
		if (sql.length() > 0) {
			sql.delete(sql.length()-5, sql.length());
			return mBookProxy.query(mDBConnection, sql.toString());
		}
		
		return null;
	}

	@Override
	public List<Book> queryBoughtBooks(User pUser) throws SQLException {
		List<Order> orders = mOrderProxy.query(mDBConnection, "id=" + pUser.getId());
		List<Book> books = new LinkedList<>();
		
		for (Order o : orders) {
			Map<Long, Integer> boughtBooks = o.getBoughtBooks();
			for (Long bookId : boughtBooks.keySet()) {
				List<Book> idBooks = mBookProxy.query(mDBConnection, "id=" + bookId);
				if (idBooks.size() > 0) {
					Book b = idBooks.get(0);
					b.setCount(boughtBooks.get(b));
					books.addAll(idBooks);
				}
			}
		}
		
		return books;
	}

	@Override
	public Order insertOrder(User pUser, Date pDate, Map<Book, Integer> bookAndCount, String pAddress, int pDiscountCoupons, int pEqualCoupns) throws SQLException {
		Order order = new Order(pUser.getId());
		
		if (pDate == null) pDate = Calendar.getInstance().getTime();
		order.setDate(pDate);
		
		for (Book key : bookAndCount.keySet()) {
			order.addBookCount(key, bookAndCount.get(key));
		}
		
		order.setAddress(pAddress);
		order.setDiscount_coupons_count(pDiscountCoupons);
		order.setEqual_coupons_count(pEqualCoupns);
		
		return mOrderProxy.insert(order, mDBConnection);
	}

	@Override
	public int update(Order pOrder) throws SQLException {
		return mOrderProxy.update(pOrder, mDBConnection);
	}

	@Override
	public int drop(Order pOrder) throws SQLException {
		return mOrderProxy.drop(pOrder, mDBConnection);
	}

	@Override
	public List<Order> queryOrders(long pUserID, Date pDate, OrderState pState) throws SQLException {
		StringBuffer sql = new StringBuffer();
		if (pUserID > 0) {
			sql.append("id=" + pUserID + " AND ");
		}
		
		if (pDate != null) {
			sql.append("date=" + StringParser.toString(pDate) + " AND ");
		}
		
		if (pState != null) {
			sql.append("state='" + pState + "' AND ");
		}
		
		if (sql.length() > 0) {
			sql.delete(sql.length()-5, sql.length());
			return mOrderProxy.query(mDBConnection, sql.toString());
		}
		
		return new LinkedList<>();
	}
	
	
}
